package batch.imei;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.*;
import org.apache.log4j.*;

import java.util.ArrayList;
import java.sql.*;
import java.io.*;
import java.sql.Connection;

public class insertImeiForTur
{
    private final String FILE_PATH  = "/home01/sfc/batch/imei/data/tur_20061013_imei_";
    private final String TABLE_NAME = "IMEI";
    //private final String ORAURL = "jdbc:oracle:thin:@eu-stgdb:1521:ORAEU";
    private final String ORAURL = "jdbc:oracle:thin:@eu-anydb:1521:ORAEU";

    public static void main(String[] args)
    {
        Logger.getLogger("imei.insertImeiForTur").info("( * ) Start.");

        insertImeiForTur obj = new insertImeiForTur();
        obj.init();

        Logger.getLogger("imei.insertImeiForTur").info("( * ) End.");
    }


    private void init(){
        Connection conn = null;

        BufferedReader bfrdForFile = null;

        int iRE = 0;
        int iIstCnt = 0;
        int i = 0;

        ArrayList params = new ArrayList();

        String strTemp = "";
        StringBuffer sQuery = new StringBuffer()
                             .append("SELECT COUNT(*)           \n")
                             .append("  FROM " + TABLE_NAME + " \n")
                             .append(" WHERE IMEI = ?           \n");

        StringBuffer iQuery = new StringBuffer()
                             .append("INSERT INTO " + TABLE_NAME + "( ID               \n")
                             .append("                              , IMEI             \n")
                             .append("                              , DIST)            \n")
                             .append("                        VALUES( IMEI_SEQ.NEXTVAL \n")
                             .append("                              , ?                \n")
                             .append("                              , ?)               \n");

        String fileName = "";

        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(ORAURL, "turkey_db", "Turk2y09");
            conn.setAutoCommit(true);

            QueryRunner qRunner = new QueryRunner();

            int iCnt = 0;

            while(true){
                iCnt++;

                fileName = FILE_PATH + iCnt + ".txt";

                if(!(new File(fileName)).exists()){
                    break;
                }

                Logger.getLogger("imei.insertImeiForTur").info(fileName);

                bfrdForFile = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), "ISO-8859-1"));

                for(i=0; (strTemp = bfrdForFile.readLine()) != null; i++) {
                    String arrStrData[] = StringUtils.splitPreserveAllTokens(strTemp, ",");

                    for(int l=0;l<arrStrData.length;l++){
                        arrStrData[l] = StringUtils.stripToEmpty(arrStrData[l]);
                    }

                    params.clear();
                    params.add(arrStrData[0]);

                    ResultSetHandler rsh = new ScalarHandler(1);

                    iRE = ((Number)qRunner.query(conn, sQuery.toString(), params.toArray(), rsh)).intValue();

                    if(iRE == 0){
                        params.clear();
                        params.add(StringUtils.deleteWhitespace(arrStrData[0]));
                        params.add(StringUtils.deleteWhitespace(arrStrData[1]));
                        //System.out.println("arrStrData[0]==" + StringUtils.deleteWhitespace(arrStrData[0]));
                        //System.out.println("arrStrData[1]==" + StringUtils.deleteWhitespace(arrStrData[1]));

                        qRunner.update(conn, iQuery.toString(), params.toArray());

                        conn.commit();

                        iIstCnt++;
                    }

                    if(i % 500 == 0){
                        Logger.getLogger("imei.insertImeiForTur").info("here we are == " + i);
                    }
                }
            }

            Logger.getLogger("imei.insertImeiForTur").info("Total ==> " + i);
            Logger.getLogger("imei.insertImeiForTur").info("Insert ==> " + iIstCnt);
        }
        catch(Exception ex) {
            if(conn != null) try { conn.rollback(); } catch(Exception ee) {}
            ex.printStackTrace();
            Logger.getLogger("imei.insertImeiForTur").info(ex.toString());
        }
        finally {
            if (bfrdForFile != null) try { bfrdForFile.close(); } catch (Exception se) {}
            if (conn != null)        try { conn.close();        } catch (Exception se) {}
        }
    }
}
